CREATE OR REPLACE PACKAGE LotIdListMaint IS

--===============================================================
-- Package information
--===============================================================
FUNCTION Version RETURN VARCHAR2
;
--===============================================================
-- Type declarations
--===============================================================

--===============================================================
-- Function and Procedure declarations
--===============================================================
FUNCTION VerifyData (LotId IN VARCHAR2
                     , EndLotId IN VARCHAR2)
RETURN VARCHAR2
;
PROCEDURE VerifyData2 (LotId IN VARCHAR2
                       , EndLotId IN VARCHAR2)
;
FUNCTION ExecuteData (ContainerNames IN VARCHAR2)
RETURN VARCHAR2
;
PROCEDURE ExecuteData2 (ContainerNames IN VARCHAR2)
;
--===========================================================================
-- End of package specification
--===========================================================================
END;
/
CREATE OR REPLACE PACKAGE BODY LotIdListMaint AS

--=====================
-- Package information
--=====================
FUNCTION Version RETURN VARCHAR2 IS BEGIN RETURN ('3.4.0001'); END;

--=====================
-- VerifyData
--=====================
FUNCTION VerifyData (LotId IN VARCHAR2
                     , EndLotId IN VARCHAR2)
RETURN VARCHAR2 IS
    --=====================
    -- Input parameter variables
    --=====================

    --=====================
    -- Local variables
    --=====================

    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Call the procedure
    --=====================
    VerifyData2 (LotId
                 , EndLotId);
    --=====================
    -- Return success
    --=====================
    RETURN ('0');
EXCEPTION
    WHEN eExit THEN
        RETURN ('LotIdListMaint.VerifyData->Exited prematurely');
    WHEN eAppError THEN
        RETURN (REPLACE(SQLERRM, 'ORA-20005: '));
    WHEN OTHERS THEN
        RETURN ('LotIdListMaint.VerifyData->' || SQLERRM);
END VerifyData;

--=====================
-- VerifyData2
--=====================
PROCEDURE VerifyData2 (LotId IN VARCHAR2
                       , EndLotId IN VARCHAR2)
IS
    --=====================
    -- Input parameter variables
    --=====================
    vLotId A_LotIdList.LotIdListName%TYPE := LotId;
    vEndLotId A_LotIdList.EndLotId%TYPE := EndLotId;
    --=====================
    -- Cursor declarations
    --=====================
    CURSOR curLotId IS
    SELECT LotIdListName, EndLotId FROM A_LotIdList WHERE LotIdListName = vLotId;
    CURSOR curCheck1 IS
    SELECT LotIdListName, EndLotId FROM A_LotIdList WHERE vLotId >= LotIdListName AND vLotId <= EndLotId;
    CURSOR curCheck2 IS
    SELECT LotIdListName, EndLotId FROM A_LotIdList WHERE vEndLotId >= LotIdListName AND vEndLotId <= EndLotId AND LotIdListName <> vLotId;
    CURSOR curCheck3 IS
    SELECT LotIdListName, EndLotId FROM A_LotIdList WHERE LotIdListName >= vLotId AND LotIdListName <= vEndLotId AND LotIdListName <> vLotId;
    CURSOR curCheck4 IS
    SELECT LotIdListName, EndLotId FROM A_LotIdList WHERE EndLotId >= vLotId AND EndLotId <= vEndLotId AND LotIdListName <> vLotId;
    --=====================
    -- Local variables
    --=====================
    rLotId curLotId%ROWTYPE;
    rCheck1 curCheck1%ROWTYPE;
    rCheck2 curCheck2%ROWTYPE;
    rCheck3 curCheck3%ROWTYPE;
    rCheck4 curCheck4%ROWTYPE;
    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Check whether the lot id already exist
    --=====================
    OPEN curLotId;
    FETCH curLotId INTO rLotId;
    CLOSE curLotId;
    --=====================
    -- Verification for new lot id
    --=====================
    IF rLotId.LotIdListName IS NULL THEN
        --=====================
        -- Verify check 1
        --=====================
        OPEN curCheck1;
        FETCH curCheck1 INTO rCheck1;
        IF curCheck1%NOTFOUND THEN
            CLOSE curCheck1;
        ELSE
            CLOSE curCheck1;
            RAISE_APPLICATION_ERROR (-20005, 'LotIdListChanges_E1001 ' || vLotId || ' ' || rCheck1.LotIdListName || ' ' || rCheck1.EndLotId);
        END IF;
    END IF;
    --=====================
    -- Verify check 2
    --=====================
    OPEN curCheck2;
    FETCH curCheck2 INTO rCheck2;
    IF curCheck2%NOTFOUND THEN
        CLOSE curCheck2;
    ELSE
        CLOSE curCheck2;
        RAISE_APPLICATION_ERROR (-20005, 'LotIdListChanges_E1002 ' || vLotId || ' ' || vEndLotId || ' ' || rCheck2.LotIdListName || ' ' || rCheck2.EndLotId);
    END IF;
    --=====================
    -- Verify check 3
    --=====================
    OPEN curCheck3;
    FETCH curCheck3 INTO rCheck3;
    IF curCheck3%NOTFOUND THEN
        CLOSE curCheck3;
    ELSE
        CLOSE curCheck3;
        RAISE_APPLICATION_ERROR (-20005, 'LotIdListChanges_E1001 ' || rCheck3.LotIdListName || ' ' || vLotId || ' ' || vEndLotId);
    END IF;
    --=====================
    -- Verify check 4
    --=====================
    OPEN curCheck4;
    FETCH curCheck4 INTO rCheck4;
    IF curCheck4%NOTFOUND THEN
        CLOSE curCheck4;
    ELSE
        CLOSE curCheck4;
        RAISE_APPLICATION_ERROR (-20005, 'LotIdListChanges_E1002 ' || rCheck4.LotIdListName || ' ' || rCheck4.EndLotId || ' ' || vLotId || ' ' || vEndLotId);
    END IF;
EXCEPTION
    WHEN eAppError THEN
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR (-20005, 'LotIdListMaint.VerifyData2->' || SQLERRM);
END VerifyData2;

--=====================
-- ExecuteData
--=====================
FUNCTION ExecuteData (ContainerNames IN VARCHAR2)
RETURN VARCHAR2 IS
    --=====================
    -- Input parameter variables
    --=====================

    --=====================
    -- Local variables
    --=====================

    --=====================
    -- Pragma Declarations
    --=====================
    PRAGMA AUTONOMOUS_TRANSACTION;
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Call the procedure
    --=====================
    ExecuteData2 (ContainerNames);
    --=====================
    -- Return success
    --=====================
    RETURN ('0');
EXCEPTION
    WHEN eExit THEN
        ROLLBACK;
        RETURN ('LotIdListMaint.ExecuteData->Exited prematurely');
    WHEN eAppError THEN
        ROLLBACK;
        RETURN (REPLACE(SQLERRM, 'ORA-20005: '));
    WHEN OTHERS THEN
        ROLLBACK;
        RETURN ('LotIdListMaint.ExecuteData->' || SQLERRM);
END ExecuteData;

--=====================
-- ExecuteData2
--=====================
PROCEDURE ExecuteData2 (ContainerNames IN VARCHAR2)
IS
    --=====================
    -- Input parameter variables
    --=====================

    --=====================
    -- Local variables
    --=====================

    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    --
    --=====================

    --=====================
    -- Commit the data
    --=====================
    COMMIT;
EXCEPTION
    WHEN eExit THEN
        ROLLBACK;
    WHEN eAppError THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'LotIdListMaint.ExecuteData2->' || SQLERRM);
END ExecuteData2;

--===========================================================================
-- End of package body
--===========================================================================
END;
/
